/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package control;

import view.Main;
import model.Address;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Mikkel & Jeppe
 */
public class AddressHandler {

    private final static String TABEL = "Address";
    private SQLCommunication sqlc;
    private ArrayList<Address> AddressList;

    public AddressHandler(Main main) {
        this.sqlc = main.getSql();
        AddressList = new ArrayList<>();
    }

    private void instantierAddressList() {
        AddressList.clear();
        int ID;
        String company;
        String address;
        String zipcode;
        String city;
        String district;
        String country;

        String sql = "SELECT * FROM " + TABEL;
        ResultSet rs = sqlc.select(TABEL, sql);

        try {
            while (rs.next()) {
                ID = rs.getInt("ID");
                company = rs.getString("company");
                address = rs.getString("Address");
                zipcode = rs.getString("Zipcode");
                city = rs.getString("City");
                district = rs.getString("District");
                country = rs.getString("country");
                Address aAddress = new Address(ID, company, address, zipcode, city, district, country);
                AddressList.add(aAddress);
            }
        } catch (SQLException ex) {
            System.out.println("Fejl ved instantiering af AddressList");
        }
    }

    public Address getAddressByID(int ID) {
        Address aAddress = null;
        String company;
        String address;
        String zipcode;
        String city;
        String district;
        String country;

        String sql = "SELECT * FROM " + TABEL + " WHERE ID = " + ID;
        ResultSet rs = sqlc.select(TABEL, sql);

        try {
            while (rs.next()) {
                ID = rs.getInt("ID");
                company = rs.getString("company");
                address = rs.getString("Address");
                zipcode = rs.getString("Zipcode");
                city = rs.getString("City");
                district = rs.getString("District");
                country = rs.getString("country");
                aAddress = new Address(ID, company, address, zipcode, city, district, country);
            }

        } catch (SQLException ex) {
            System.out.println("Fejl ved hentning af Adresse");
        }
        return aAddress;
    }

    public Address getAddressByCompany(String company) {
        Address aAddress = null;
        int ID;
        String address;
        String zipcode;
        String city;
        String district;
        String country;

        String sql = "SELECT * FROM " + TABEL + " WHERE company = '" + company + "'";
        ResultSet rs = sqlc.select(TABEL, sql);
        if (rs != null) {
            try {
                if (rs.next()) {
                    ID = rs.getInt("ID");
                    company = rs.getString("company");
                    address = rs.getString("Address");
                    zipcode = rs.getString("Zipcode");
                    city = rs.getString("City");
                    district = rs.getString("District");
                    country = rs.getString("country");
                    aAddress = new Address(ID, company, address, zipcode, city, district, country);
                }
                rs.close();
            } catch (SQLException ex) {
                System.out.println("Fejl ved hentning af Adresse");
            }
        }
        return aAddress;
    }

    public boolean tjekAddressByID(int ID) {
        boolean exists = false;
        String sql = "SELECT * FROM " + TABEL + " WHERE ID = " + ID;
        ResultSet rs = sqlc.select(TABEL, sql);

        try {
            if (rs.next()) {
                exists = true;
            }
        } catch (SQLException ex) {
            System.out.println("Kunne ikke finde nogen addresse med ID=" + ID);
        }
        return exists;
    }

    public boolean tjekAddressByAllParameters(String company, String address, String zipcode, String city, String district, String country) {
        boolean exists = false;
        String sql;
        if (company.equals("") && district.equals("") || company == null && district == null) {
            sql = "SELECT * FROM " + TABEL + " "
                    + "WHERE address = '" + address + "' "
                    + "AND Zipcode = '" + zipcode + "' "
                    + "AND City = '" + city + "' "
                    + "AND Country = '" + country + "'";
        } else if (company.equals("") || company == null) {
            sql = "SELECT * FROM " + TABEL + " "
                    + "WHERE address = '" + address + "' "
                    + "AND Zipcode = '" + zipcode + "' "
                    + "AND City = '" + city + "' "
                    + "AND District = '" + district + "' "
                    + "AND Country = '" + country + "'";
        } else if (district.equals("")) {
            sql = "SELECT * FROM " + TABEL + " "
                    + "WHERE Company = '" + company + "' "
                    + "AND address = '" + address + "' "
                    + "AND Zipcode = '" + zipcode + "' "
                    + "AND City = '" + city + "' "
                    + "AND Country = '" + country + "'";
        } else {
            sql = "SELECT * FROM " + TABEL + " "
                    + "WHERE Company = '" + company + "' "
                    + "AND address = '" + address + "' "
                    + "AND Zipcode = '" + zipcode + "' "
                    + "AND City = '" + city + "' "
                    + "AND District = '" + district + "' "
                    + "AND Country = '" + country + "'";
        }

        ResultSet rs = sqlc.select(TABEL, sql);

        try {
            if (rs.next()) {
                exists = true;
            }
        } catch (SQLException ex) {
            System.out.println("Kunne ikke finde nogen addresse med disse parametre" + sql);
        }
        return exists;
    }

    public boolean tjekAddressByCompany(String company) {
        boolean exists = false;
        if (company != null || !company.equals("")) {
            String sql;
            sql = "SELECT * FROM " + TABEL + " WHERE company = '" + company + "'";
            ResultSet rs = sqlc.select(TABEL, sql);

            try {
                if (rs.next()) {
                    exists = true;
                }
            } catch (SQLException ex) {
                System.out.println("Der findes ingen addresse hvor company = " + company);
            }
        }
        return exists;
    }

    public Address getAddressByAllParameters(String company, String address, String zipcode, String city, String district, String country) {
        Address aAddress = null;
        String sql;
        if (company == null && district == null || company.equals("") && district.equals("")) {
            sql = "SELECT * FROM " + TABEL + " "
                    + "WHERE address like '" + address + "' "
                    + "AND Zipcode = '" + zipcode + "' "
                    + "AND City like '" + city + "' "
                    + "AND Country like '" + country + "'";
        } else if (company.equals("") || company == null) {
            sql = "SELECT * FROM " + TABEL + " "
                    + "WHERE address like '" + address + "' "
                    + "AND Zipcode = '" + zipcode + "' "
                    + "AND City like '" + city + "' "
                    + "AND District like '" + district + "' "
                    + "AND Country like '" + country + "'";
        } else if (district.equals("")) {
            sql = "SELECT * FROM " + TABEL + " "
                    + "WHERE Company like '" + company + "' "
                    + "AND address like '" + address + "' "
                    + "AND Zipcode = '" + zipcode + "' "
                    + "AND City like '" + city + "' "
                    + "AND Country like '" + country + "'";
        } else {
            sql = "SELECT * FROM " + TABEL + " "
                    + "WHERE Company like '" + company + "' "
                    + "AND address like '" + address + "' "
                    + "AND Zipcode = '" + zipcode + "' "
                    + "AND City like '" + city + "' "
                    + "AND District like '" + district + "' "
                    + "AND Country like '" + country + "'";
        }

        ResultSet rs = sqlc.select(TABEL, sql);

        try {
            if (rs.next()) {
                aAddress = new Address(rs.getInt("ID"),
                        rs.getString("Company"),
                        rs.getString("Address"),
                        rs.getString("Zipcode"),
                        rs.getString("City"),
                        rs.getString("District"),
                        rs.getString("Country"));
            }
        } catch (SQLException ex) {
            System.out.println("Kunne ikke finde nogen addresse med disse parametre" + sql);
        }
        return aAddress;
    }

    public void insertToDB(Address address) {
        System.out.println(address.toString());
        if (tjekAddressByAllParameters(address.getCompany(), address.getAddress(), address.getZipcode(), address.getCity(), address.getDistrict(), address.getCountry())) {
        } else {
            String sql = "insert into " + TABEL + " (company, address, zipcode, city, district, country) values" + address.toStringSQL();
            sqlc.insert(TABEL, sql);
        }

    }
}
